﻿create database Goldtime_Data
use Goldtime_Data
go
create table Users 
(
STTU int identity ,
UserID varchar(50) primary key,
EmployeeID varchar(50) ,
Username varchar(50),
Password varchar(50),
constraint FK_Users foreign key(EmployeeID) references Employee(EmployeeID),
)
select * from Users
drop table Users
go

go
create table Departments
(
STTD int identity ,
DepartmentID nvarchar(50) primary key,
DepartmentName nvarchar(50),
)
go
go
create table Employee
(
STTS int identity ,
EmployeeID varchar(50) primary key,
EmployeeName varchar(50),
EmployeeAddress varchar(50),
PhoneNumber int,
DepartmentID varchar(50) ,--Lien ket den bang danhmucphongban
)
go
insert into Employee values ('NHa1','Nguyen Huu Lam','Ha Noi',12345,'1')
insert into Employee values ('NH0001','Nguyen Van A','Ha Noi',0910293,'2')
select * from Employee
go
create table Customer 
(
STTC int identity,
CustomersID varchar(50)  primary key,
CustomerName nvarchar(50),
CustomerAddress nvarchar(50),
Phonenumber int,
)
select *from Customer
go
go
create table List_VotesSent
(
STTL int identity primary key,
Waybill varchar(50) ,
Date varchar(50),
StatusTT varchar(100),
StatusVH varchar(100),
EmployeeIDGN varchar(50) ,
EmployeeIDKT varchar(50),
)
go
insert into List_VotesSent values ('NH01','04/10/2013','','','','')
insert into List_VotesSent values ('NH02','04/10/2013','','','','')
insert into List_VotesSent values ('NH03','05/10/2013','','','','')
insert into List_VotesSent values ('NH04','05/10/2013','','','','')
insert into List_VotesSent values ('NH05','04/11/2013','','','','')
select *from List_VotesSent
go
create table VotesSent
(
STTV int identity ,
Waybill varchar(50) primary key,--Ma so phieu gui
Date varchar(50),--Ngay gui
EmployeeID varchar(50),
CustomersID varchar(50),
DeliveryAddress varchar(50),
GDistanceID varchar(50),
TypeParceID varchar(50),
GroupWeightID varchar(50), --trong luong buu pham
CostsIncurred float,-- Cac loai phi phat sinh
ShippingFee float,
Totalamount float,
)
select * from VotesSent
go
CREATE TRIGGER Coppy
ON VotesSent
FOR INSERT
AS
BEGIN
declare @STT int
 SELECT @STT=MAX(STTV) FROM VotesSent

INSERT INTO List_VotesSent (Waybill,Date) SELECT Waybill,Date FROM VotesSent where VotesSent.STTV= @STT
END
go
create table Prices_by_Area
(
STTA int identity ,
GAreaID varchar(50) primary key,
GAreaName varchar(50),
Distance float,
)
go
insert into Prices_by_Area values ('NH01','aaaa',100)
insert into Prices_by_Area values ('NH02','bbbb',200)
insert into Prices_by_Area values ('NH03','cccc',300)
insert into Prices_by_Area values ('NH04','dddd',400)
go
create table Prices_by_distance
(
STTR int identity ,
GDistanceID varchar(50) primary key,
GDistance varchar(50),
PriceOfGDistance float,
)
go
insert into Prices_by_distance values ('NH01','0-100',23)
insert into Prices_by_distance values ('NH02','100-150',30)
insert into Prices_by_distance values ('NH03','150-200',40)
insert into Prices_by_distance values ('NH04','200-250',50)
select * from Prices_by_distance
go
create table Prices_by_weight
(
STTW int identity ,
GroupWeightID varchar(50) primary key,
GroupWeightName varchar(50),
PriceOfGroupWeight float,
)
select * from Prices_by_weight
go
create table List_of_Parce
(
STTP int identity,
TypeParceID varchar(50)  primary key,--ma loai buu pham
TypeName varchar(50),-- ten loai buu pham
PriceOfTypeParce float,
)
go

SELECT        Users.STTU, VotesSent.*, List_VotesSent.*, Employee.*, Departments.*, Prices_by_distance.*, List_of_Parce.*, Prices_by_weight.*, Prices_by_Area.*, 
                         Customer.*
FROM            Prices_by_Area INNER JOIN
                         Users INNER JOIN
                         Employee ON Users.EmployeeID = Employee.EmployeeID INNER JOIN
                         VotesSent ON Employee.EmployeeID = VotesSent.EmployeeID INNER JOIN
                         Departments ON Employee.DepartmentID = Departments.DepartmentID INNER JOIN
                         Customer ON VotesSent.CustomersID = Customer.CustomersID INNER JOIN
                         List_of_Parce ON VotesSent.TypeParceID = List_of_Parce.TypeParceID INNER JOIN
                         Prices_by_distance ON VotesSent.GDistanceID = Prices_by_distance.GDistanceID INNER JOIN
                         Prices_by_weight ON VotesSent.GroupWeightID = Prices_by_weight.GroupWeightID INNER JOIN
                         List_VotesSent ON VotesSent.Waybill = List_VotesSent.Waybill AND Employee.EmployeeID = List_VotesSent.EmployeeIDGN AND 
                         Employee.EmployeeID = List_VotesSent.EmployeeIDKT ON Prices_by_Area.Distance = Prices_by_distance.GDistanceID
